-- @owner: ningyali
-- @date: 2025-1-17
-- @testpoint: HTAP行列融合特性--转换前后创建触发器引用表数据，数据更新验证

--step1: 创建表A/B expect: 执行成功
@conn SingleDbUser;
drop table if exists t_htap_single_0155_a cascade;
drop table if exists t_htap_single_0155_b cascade;
create table t_htap_single_0155_a(c1 int, c2 varchar(30), c3 timestamp);
create table t_htap_single_0155_b(c1 int, c2 varchar(30), c3 timestamp);

--step2: 创建触发器函数和触发器 expect: 执行成功
create or replace function func_htap_single_0155_insert() 
returns trigger as
$$
declare
begin
    insert into t_htap_single_0155_a values(new.c1, new.c2, new.c3);
    return new;
end
$$ language plpgsql;
/
           
create or replace function func_htap_single_0155_update() 
returns trigger as
$$
declare
begin
    update t_htap_single_0155_a set c2=new.c2 where c1=old.c1;
    return old;
end
$$ language plpgsql;
/
           
create or replace function func_htap_single_0155_delete() 
returns trigger as
$$
declare
begin
    delete from t_htap_single_0155_a where c1=old.c1;
    return old;
end
$$ language plpgsql;
/
           
create trigger tri_htap_single_0155_insert
before insert on t_htap_single_0155_b
for each row
execute procedure func_htap_single_0155_insert();
/
           
create trigger tri_htap_single_0155_update
after update on t_htap_single_0155_b  
for each row
execute procedure func_htap_single_0155_update();
/
           
create trigger tri_htap_single_0155_delete
before delete on t_htap_single_0155_b
for each row
execute procedure func_htap_single_0155_delete();
/

--step3: 表B插入数据 expect: 执行成功
insert into t_htap_single_0155_b values (
generate_series(1, 10000), 'first insert'||generate_series(1, 10000), now());

--step4: 表A、B部分列进行行列转换 expect: 执行成功
alter table t_htap_single_0155_a imcstored(c1);
alter table t_htap_single_0155_b imcstored(c2, c3);

--step5: 查询 expect: 执行成功
set enable_imcsscan to on;
explain (costs false) select count(c1) from t_htap_single_0155_a where c1>=88 and c1<=95869;
explain (costs false) select count(c2) from t_htap_single_0155_b where c2 like '%00' and c3 is not null;

select count(c1) from t_htap_single_0155_a where c1>=88 and c1<=95869;
select count(c2) from t_htap_single_0155_b where c2 like '%00' and c3 is not null;

--step6: 对表B数据进行修改 expect: 执行成功
update t_htap_single_0155_b set c2='first update' where c1%2=0;

--step7: 查询 expect: 执行成功
set enable_imcsscan to on;
explain (costs false) select count(c1) from t_htap_single_0155_a where c1>=88 and c1<=95869;
explain (costs false) select count(c2) from t_htap_single_0155_b where c2 like '%00' and c3 is not null;

select count(c1) from t_htap_single_0155_a where c1>=88 and c1<=95869;
select count(c2) from t_htap_single_0155_b where c2 like '%00' and c3 is not null;

--step8: 取消转换 expect: 执行成功
alter table t_htap_single_0155_a unimcstored;
alter table t_htap_single_0155_b unimcstored;

--step9: 全表转换 expect: 执行成功
alter table t_htap_single_0155_a imcstored;
alter table t_htap_single_0155_b imcstored;

--step10: 查询 expect: 执行成功
set enable_imcsscan to on;
explain (costs false) select count(c1) from t_htap_single_0155_a where c1>=88 and c1<=95869;
explain (costs false) select count(c2) from t_htap_single_0155_b where c2 like '%00' and c3 is not null;

select count(c1) from t_htap_single_0155_a where c1>=88 and c1<=95869;
select count(c2) from t_htap_single_0155_b where c2 like '%00' and c3 is not null;

--step11: 删除部分表B数据 expect: 执行成功
delete from t_htap_single_0155_b where c1>8000;

--step12: 查询 expect: 执行成功
set enable_imcsscan to on;
explain (costs false) select count(c1) from t_htap_single_0155_a where c1>=88 and c1<=95869;
explain (costs false) select count(c2) from t_htap_single_0155_b where c2 like '%00' and c3 is not null;

select count(c1) from t_htap_single_0155_a where c1>=88 and c1<=95869;
select count(c2) from t_htap_single_0155_b where c2 like '%00' and c3 is not null;

--step13: 清理环境 expect: 执行成功
drop trigger tri_htap_single_0155_insert on t_htap_single_0155_b;
drop trigger tri_htap_single_0155_update on t_htap_single_0155_b;
drop trigger tri_htap_single_0155_delete on t_htap_single_0155_b;
drop function if exists func_htap_single_0155_insert;
drop function if exists func_htap_single_0155_update;
drop function if exists func_htap_single_0155_delete;
drop table if exists t_htap_single_0155_a cascade;
drop table if exists t_htap_single_0155_b cascade;